﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Language;
using Portal.Environment;

namespace Portal.CDSP
{
    public class VideoBLL
    {
        public DataTable ReadingForSearch(string belongSite,string ActiveLanguage)
        {
            string Query = "select Id=AdvImageId,Subject=IsNull([" + ActiveLanguage + "Subject],'') from TableAdvImage where belongSite=@belongSite and AdvImageId in (select fk_AdvImage_Id from TableAdvImageItem)";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForService(string belongSite, string ActiveLanguage)
        {
            string Query = "select Id=AlbumId,Subject=[" + ActiveLanguage + "Subject] from TableAlbum where belongSite='" + belongSite + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;

        }

        public DataTable ReadingItems(string AdvImageId)
        {
            string Query = "select * from TableAdvImageItem where IsPublishing=1 and ( left(fk_AdvImage_Id,8)=@AdvImageId  or fk_AdvImage_Id=@AdvImageId) order by IndexOrder";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingVideoItems(string AdvImageId)
        {
            string Query = "select * from TableAdvImageItem where IsPublishing=1 and left(AdvImageItemId,8)=@AdvImageId order by IndexOrder";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingRelation(string AdvImageId, string fk_AdvImage_Id)
        {
            string Query = "select * from TableAdvImageItem where IsPublishing=1 and (left(fk_AdvImage_Id,8)=@fk_AdvImage_Id or fk_AdvImage_Id=@fk_AdvImage_Id) and left(AdvImageItemId,8)<>@AdvImageId order by IndexOrder";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@fk_AdvImage_Id", SqlDbType.VarChar);
            sqlCommand.Parameters["@fk_AdvImage_Id"].Value = fk_AdvImage_Id;

            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        /// <summary>
        /// Lấy đường dẫn của ảnh
        /// </summary>
        /// <param name="ShortId">Biến Id của dữ liệu = 8 kí tự đầu tiên của ID dài 36 kí tự</param>
        /// <returns>Đường dẫn theo chuẩn http://</returns>
        public string GetAdvImageUrl(string ShortId)
        {
            string Query = "select * from TableAdvImageItem where left(AdvImageItemId,8)='" + ShortId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count <= 0)
            {
                return "";
            }
            else
            {
                string AdvImageId=(string)dtResult.Rows[0]["fk_AdvImage_Id"];
                string FileImage = (string)dtResult.Rows[0]["FileImage"];
                string belongSite = (string)dtResult.Rows[0]["belongSite"];
                string Url = (string)dtResult.Rows[0]["Url"];
                string ImageUrl = Environments.HttpMediaRoot + "/Media/" + Environments.PortalAlias + "/AdvImage/Images/" + belongSite + "/" + AdvImageId + "/" + FileImage;
                return ImageUrl;
            }
        }
    }
}
